/**
 * Created by jackliu on 15/12/16.
 */

const common = require('../../../../../../../sjgsvn/server/common/common');
const pool = require('../index');
const mysql = require('mysql');

class YjxBaseModel {
    constructor(obj) {
        common.copyProperty(this, obj);
    }


    // 取得 DB 名称
    getDBName() {
        return 'yjx'
    };

// 取得表名（需要各派生类进行重载）
    getTableName () {
        return '';
    };


// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
    getWhereConditionOfKeyEqual(){
        return '1=0';
    };
// 取得查询语句中where 后面的条件
    getWhereCondition() {
        let ret = "1=1";
        for (const key in this){
            const value = this[key];
            if(value!==null && (typeof value!=='function'))
                ret +=(" and `"+key+"`="+"'"+value+"'");
        }

        return ret;

    };
// 取得排序方法（需要各派生类进行重载）
    getOrderBy() {
        return '';
    };
// 新建到DB中
// callback 参数：
//                error：错误
//                id：db的id
    saveToDB (callback) {
        const THIS = this;
        const tableName = this.getDBName() + '.' + this.getTableName();
        pool.getConnection(function(getConnectionErr, connection) {
            if (getConnectionErr) {
                return callback(getConnectionErr, null);
            }
            // Use the connection
            let sql = 'INSERT INTO '+tableName+' SET ? ,create_time=sysdate(),update_time=sysdate()';
            let sqlValue = [ THIS ];
            sql = mysql.format(sql, sqlValue);
            console.log(sql);
            connection.query(sql, function(err, result) {
                // And done with the connection.
                connection.release();

                if (err) {
                    console.log(err);
                    return callback(err, null);
                }
                callback(err, result.insertId);
            });
        });

    };

    async saveToDBPromise() {
        const self = this;
        return new Promise((resolve, reject) => {
            self.saveToDB((error, data) => {
                error ? reject(error) : resolve(data);
            });
        });
    };


// 更新到DB中
// callback 参数：
//                error：错误
    updateToDB(callback){

        const THIS = this;
        const tableName = this.getDBName() + '.' + this.getTableName();
        //mysql update
        const whereConditionOfKeyEqual = THIS.getWhereConditionOfKeyEqual();
        pool.getConnection(function(getConnectionErr, connection) {
            // Use the connection
            if (getConnectionErr) {
                return callback(getConnectionErr, null);
            }

            let sql = 'UPDATE '+tableName+' SET ? ,update_time=sysdate() WHERE '+whereConditionOfKeyEqual;
            let sqlValue = [ THIS ];
            sql = mysql.format(sql, sqlValue);
            console.log(sql);
            connection.query(sql, function(error, result) {
                // And done with the connection.
                connection.release();

                if(error) {
                    console.log('updateToDB error:'+error);
                    callback(error);
                } else {
                    callback(null);
                }
            });
        });
    };

    async updateToDBPromise () {
        const self = this;
        return new Promise((resolve, reject) => {
            self.updateToDB((error, data) => {
                error ? reject(error) : resolve(data);
            });
        });
    };


// arrUpdSysDtFieldNm: 需更新成sysdate()的字段名数组: ["finish_time","update_time"]
    updateToDB_SysDt(callback) {

        const THIS = this;
        const tableName = this.getDBName() + '.' + this.getTableName();
        //mysql update
        const whereConditionOfKeyEqual = THIS.getWhereConditionOfKeyEqual();
        pool.getConnection(function(getConnectionErr, connection) {
            // Use the connection
            if (getConnectionErr) {
                return callback(getConnectionErr, null);
            }
            let sql_0 = '';
            if(THIS.arrUpdSysDtFieldNm) {
                for(let i=0;i<THIS.arrUpdSysDtFieldNm.length;i++){
                    const nm = THIS.arrUpdSysDtFieldNm[i];
                    if(nm && nm.length>0) {
                        sql_0 += ','+nm+'=sysdate() ';
                    }
                }
                delete THIS.arrUpdSysDtFieldNm;
            }

            let sql = 'UPDATE '+tableName+' SET ? ,update_time=sysdate() '+sql_0+' WHERE '+whereConditionOfKeyEqual;
            let sqlValue = [ THIS ];
            sql = mysql.format(sql, sqlValue);
            console.log(sql);
            connection.query(sql, function(error, result) {
                // And done with the connection.
                connection.release();

                if(error) {
                    console.log('updateToDB_SysDt error:'+error);
                    callback(error);
                } else {
                    callback(null);
                }
            });
        });
    };
    async updateToDB_SysDtPromise() {
        const self = this;
        return new Promise((resolve, reject) => {
            self.updateToDB_SysDt((error, data) => {
                error ? reject(error) : resolve(data);
            });
        });
    };

// 查找
// callback 参数：
//                error：错误
//                count：找到的个数
    findCountFromDB(callback) {
        const THIS = this;
        const tableName = this.getDBName() + '.' + this.getTableName();
        const whereCondition = THIS.getWhereCondition();
        // 从mysql中查询
        pool.getConnection(function(getConnectionErr, connection) {
            if (getConnectionErr) {
                return callback(getConnectionErr, null);
            }

            let sql = "select count(*) as itemCount from "+tableName+" t1 where "+whereCondition;
            let sqlValue = [];
            sql = mysql.format(sql, sqlValue);
            console.log(sql);

            // Use the connection
            connection.query(sql, function(error, items) {
                // And done with the connection.
                connection.release();

                let total=0;
                if(error) {
                    console.log(error);
                } else if (items) {
                    console.log('find item');
                    total = items[0].itemCount;
                } else{
                    console.log('find no one');
                }
                // 回调
                callback(error, total);
            });

        });

    };

    async findCountFromDBPromise() {
        const self = this;
        return new Promise((resolve, reject) => {
            self.findFromDB((error, data) => {
                error ? reject(error) : resolve(data);
            })
        });
    };


// 查找
// callback 参数：
//                error：错误
//                items：找到所有对象
    findFromDB(callback,skipCnt,pageSize) {
        const THIS = this;
        const tableName = this.getDBName() + '.' + this.getTableName();
        const whereCondition = THIS.getWhereCondition();
        const orderBy = THIS.getOrderBy();
        // 从mysql中查询
        pool.getConnection(function(getConnectionErr, connection) {
            if (getConnectionErr) {
                return callback(getConnectionErr, null);
            }


            let sql = "select *  from "+tableName+" t1 where "+whereCondition;
            if(orderBy && orderBy.length>0){
                sql += (" order by "+orderBy);
            }
            if(typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
                sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
            }

            let sqlValue = [];
            sql = mysql.format(sql, sqlValue);
            console.log(sql);

            // Use the connection
            connection.query(sql, function(error, items) {
                // And done with the connection.
                connection.release();

                // 回调
                callback(error, items);
            });

        });
    };
    async findFromDBPromise (skipCnt,pageSize){
        const self = this;
        return new Promise((resolve, reject) => {
            self.findFromDB((error, data) => {
                error ? reject(error) : resolve(data);
            }, skipCnt, pageSize);
        });
    };


    getTimeFromDB (callback) {
        const THIS = this;
        // 从mysql中查询
        pool.getConnection(function(err, connection) {
            let sql = " SELECT DATE_FORMAT(sysdate(), '%Y-%m-%d %H:%i:%S') AS str_sysdate "+
                " ,DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%S') AS str_now "+
                " ,UNIX_TIMESTAMP(now()) AS ts_now "+
                " FROM DUAL ";
            console.log(sql);

            // Use the connection
            connection.query(sql, function(error, items) {
                // And done with the connection.
                connection.release();

                // 回调
                if(items && items.length>0) {
                    callback(error, items[0]);
                } else {
                    callback(error, null);
                }
            });
        });
    };

    async getTimeFromDBPromise () {
        const self = this;
        return new Promise((resolve, reject) => {
            self.getTimeFromDB((error, data) => {
                error ? reject(error) : resolve(data);
            });
        });
    };
}





// 导出
module.exports = YjxBaseModel;